import * as SQLite from 'expo-sqlite';
import { Plan, TimerRecord } from './models';

const db = SQLite.openDatabase('timetracker.db');

export const initDatabase = () => {
  return new Promise((resolve, reject) => {
    db.transaction(tx => {
      // Create plans table
      tx.executeSql(
        `CREATE TABLE IF NOT EXISTS plans (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          name TEXT NOT NULL,
          coverImage TEXT,
          createdAt TEXT NOT NULL,
          updatedAt TEXT NOT NULL
        );`
      );

      // Create timer_records table
      tx.executeSql(
        `CREATE TABLE IF NOT EXISTS timer_records (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          planId INTEGER NOT NULL,
          startTime TEXT NOT NULL,
          endTime TEXT NOT NULL,
          duration INTEGER NOT NULL,
          createdAt TEXT NOT NULL,
          FOREIGN KEY (planId) REFERENCES plans (id) ON DELETE CASCADE
        );`
      );
    }, (error) => {
      console.error('Error creating database:', error);
      reject(error);
    }, () => {
      resolve(true);
    });
  });
};

// Plans CRUD operations
export const createPlan = (plan: Omit<Plan, 'id' | 'createdAt' | 'updatedAt'>): Promise<Plan> => {
  return new Promise((resolve, reject) => {
    const now = new Date().toISOString();
    db.transaction(tx => {
      tx.executeSql(
        'INSERT INTO plans (name, coverImage, createdAt, updatedAt) VALUES (?, ?, ?, ?)',
        [plan.name, plan.coverImage || null, now, now],
        (_, result) => {
          resolve({
            id: result.insertId!,
            ...plan,
            createdAt: now,
            updatedAt: now,
          });
        },
        (_, error) => {
          reject(error);
          return false;
        }
      );
    });
  });
};

export const getPlans = (): Promise<Plan[]> => {
  return new Promise((resolve, reject) => {
    db.transaction(tx => {
      tx.executeSql(
        'SELECT * FROM plans ORDER BY updatedAt DESC',
        [],
        (_, result) => {
          resolve(result.rows._array);
        },
        (_, error) => {
          reject(error);
          return false;
        }
      );
    });
  });
};

export const updatePlan = (plan: Plan): Promise<Plan> => {
  return new Promise((resolve, reject) => {
    const now = new Date().toISOString();
    db.transaction(tx => {
      tx.executeSql(
        'UPDATE plans SET name = ?, coverImage = ?, updatedAt = ? WHERE id = ?',
        [plan.name, plan.coverImage || null, now, plan.id],
        () => {
          resolve({ ...plan, updatedAt: now });
        },
        (_, error) => {
          reject(error);
          return false;
        }
      );
    });
  });
};

export const deletePlan = (id: number): Promise<void> => {
  return new Promise((resolve, reject) => {
    db.transaction(tx => {
      tx.executeSql(
        'DELETE FROM plans WHERE id = ?',
        [id],
        () => {
          resolve();
        },
        (_, error) => {
          reject(error);
          return false;
        }
      );
    });
  });
};

// Timer records operations
export const createTimerRecord = (record: Omit<TimerRecord, 'id' | 'createdAt'>): Promise<TimerRecord> => {
  return new Promise((resolve, reject) => {
    const now = new Date().toISOString();
    db.transaction(tx => {
      tx.executeSql(
        'INSERT INTO timer_records (planId, startTime, endTime, duration, createdAt) VALUES (?, ?, ?, ?, ?)',
        [record.planId, record.startTime, record.endTime, record.duration, now],
        (_, result) => {
          resolve({
            id: result.insertId!,
            ...record,
            createdAt: now,
          });
        },
        (_, error) => {
          reject(error);
          return false;
        }
      );
    });
  });
};

export const getTimerRecords = (planId?: number): Promise<TimerRecord[]> => {
  return new Promise((resolve, reject) => {
    db.transaction(tx => {
      const query = planId
        ? 'SELECT * FROM timer_records WHERE planId = ? ORDER BY startTime DESC'
        : 'SELECT * FROM timer_records ORDER BY startTime DESC';
      const params = planId ? [planId] : [];
      
      tx.executeSql(
        query,
        params,
        (_, result) => {
          resolve(result.rows._array);
        },
        (_, error) => {
          reject(error);
          return false;
        }
      );
    });
  });
};

// Statistics queries
export const getDailyStats = (startDate: string, endDate: string): Promise<{ date: string; totalDuration: number; recordCount: number; }[]> => {
  return new Promise((resolve, reject) => {
    db.transaction(tx => {
      tx.executeSql(
        `SELECT date(startTime) as date,
                SUM(duration) as totalDuration,
                COUNT(*) as recordCount
         FROM timer_records
         WHERE date(startTime) BETWEEN date(?) AND date(?)
         GROUP BY date(startTime)
         ORDER BY date(startTime)`,
        [startDate, endDate],
        (_, result) => {
          resolve(result.rows._array);
        },
        (_, error) => {
          reject(error);
          return false;
        }
      );
    });
  });
}; 